import simplejson
import sys

from sqlalchemy import *
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from optparse import OptionParser

from vote.model import *

def init_parser():
    ''' This is a global method (called globally below) for parsing a command line (testing).  It returns
        an option dict that we use in the Vote service constructor below...
    '''
    parser = OptionParser()
    parser.set_defaults(
        create=False,
        db='sqlite:///vote.db',
        format='extended',
        schema=None,
    )
    parser.add_option(
        "-c", "--create", action="store_true", dest="create",
        help="Create database tables"
    )
    parser.add_option(
        "-d", "--database", dest="db",
        help="Database connection string for engine"
    )
    parser.add_option(
        "-f", "--csv", dest="data",
        help="CSV file name for trip parameters"
    )
    parser.add_option(
        "-s", "--schema", dest="schema",
        help="Optional database schema name"
    )
    (options, args) = parser.parse_args()
    return options

# global variables
OPTIONS = init_parser()
ERROR   = {'status': '500', 'message':'Error'}
SUCCESS = {'status': '200', 'message':'Success'}

class BaseController:
    def __init__(self, options=OPTIONS):
        self.options = options
        session = self.get_session()

        # create a new database
        if options.create:
            tables.Vote.metadata.drop_all(bind=self.db)
            tables.Vote.metadata.create_all(bind=self.db)

            # add the default user to new db
            u = User.from_dict({'name': 'Default User', 'id': BOGUS_VALUE})
            session.add(u)

            # load the vote types from the .csv file
            VoteType.load(self.db, "./vote/model/", "vote_types.csv");
            Vote.load(self.db, "./vote/model/", "seed_votes.csv");

            # commit db changes created on new db
            session.commit()
            session.flush()


    def get_session(self):
        ret_val = None
        try:
            # try 1: get session
            ret_val = self.Session()
            ret_val.query(Vote).first() # test the session (and go to try 2 if we fail)
        except:
            # try 2: reopen the database, then get session
            self.open(self.options)
            ret_val = self.Session()

        return ret_val


    def open(self, options):
        ''' open the vote database when Voter is instanced
        '''
        print "opening " + options.db
        self.db = create_engine(options.db)
        self.db.echo = False
        self.metadata = MetaData(self.db)
        self.Session = sessionmaker(bind=self.db)


    def new_vote(self, feature, type, vote, comment, user):
        ''' Insert new vote into the database
        '''
        ret_val = SUCCESS.copy()
        try:
            session = self.get_session()
            if type is BOGUS_VALUE:
                type = 1 # default value
            v = Vote.from_dict({'feature':feature, 'type':type, 'value': vote, 'comment':comment, 'user':user})
            session.add(v)
            session.commit()
            session.flush()
            ret_val["message"] = "Success inserting vote into the database"
        except Exception as detail:
            print detail
            ret_val = ERROR.copy()
            ret_val["message"] = "Error when inserting into the database"
            ret_val["detail"] = detail.__str__() 
        return ret_val


    def average_votes(self, feature, type, user=BOGUS_VALUE):
        '''  pull out vote averages from the database
        '''
        ret_val = SUCCESS.copy()
        try:
            average = 0
            session = self.get_session()
            q = session.query(Vote.type, Vote.feature, func.avg(Vote.value), func.count(Vote.value))
            if user is not None and user is not BOGUS_VALUE:
                q = q.filter(Vote.user == user)
            if feature is not None and feature is not BOGUS_VALUE:
                q = q.filter(Vote.feature == feature)
            if type is not None and type is not BOGUS_VALUE:
                q = q.filter(Vote.type == type)
            q = q.group_by(Vote.type, Vote.feature)
            ret_val.setdefault('averages', [])
            for r in q:
                ret_val['averages'].append({'type':r[0], 'feature':r[1], 'avg':round(r[2]), 'num':r[3]})
        except Exception as detail:
            print detail
            ret_val = ERROR.copy()
            ret_val["message"] = "Error reading from the database"
            ret_val["detail"] = detail.__str__() 

        return ret_val
